<?php

if (!defined('BASEPATH'))
	exit('No direct script access allowed');

class Mod_report extends PNC_Model {

	public function count_report_admin($id) {
		$query = $this->db
				->select('*')
				->from('pnc_record')
				->join('pnc_employee', 'pnc_record.staff_id = pnc_employee.staff_id')
				->join('pnc_roles', 'pnc_roles.role_id = pnc_employee.role_id')
				->where('pnc_record.staff_id', $id)
				->where('pnc_record.rcd_status', 1)
				->where('pnc_roles.role_alias', 'admin')
				->count_all_results();
		return $query;
	}

	public function num_report($leave_type, $lea_id, $staff_id) {
		if ($leave_type === 'annual') {
			$query = $this->db
					->select('*')
					->from('pnc_record')
					->join('pnc_employee', 'pnc_record.staff_id = pnc_employee.staff_id')
					->join('pnc_department', 'pnc_employee.dept_id = pnc_department.dept_id')
					->join('pnc_leave_type', 'pnc_leave_type.lea_id = pnc_record.lea_id')
					->join('pnc_sub_leave', 'pnc_sub_leave.stl_id = pnc_record.stl_id')
					->join('pnc_roles', 'pnc_roles.role_id = pnc_employee.role_id')
					->where('pnc_record.staff_id', $staff_id)
					->where('pnc_leave_type.lea_alias', $leave_type)
					->where('pnc_record.lea_id', $lea_id)
					->where('rcd_status', 1)
					->count_all_results();
		} else if ($leave_type === 'catchup') {
			$query = $this->db
					->select('*')
					->from('pnc_record')
					->join('pnc_employee', 'pnc_record.staff_id = pnc_employee.staff_id')
					->join('pnc_department', 'pnc_employee.dept_id = pnc_department.dept_id')
					->join('pnc_leave_type', 'pnc_leave_type.lea_id = pnc_record.lea_id')
					->join('pnc_cup_des', 'pnc_record.catch_desc = pnc_cup_des.desc_id')
					->where('pnc_record.staff_id', $staff_id)
					->where('pnc_leave_type.lea_alias', $leave_type)
					->where('pnc_record.lea_id', $lea_id)
					->where('rcd_status', 1)
					->count_all_results();
		}
		return $query;
	}

	public function num_annual($leave_type, $lea_id, $staff_id) {
		if ($leave_type === 'annual') {
			$query = $this->db
					->select('*')
					->from('pnc_record')
					->join('pnc_employee', 'pnc_record.staff_id = pnc_employee.staff_id')
					->join('pnc_department', 'pnc_employee.dept_id = pnc_department.dept_id')
					->join('pnc_leave_type', 'pnc_leave_type.lea_id = pnc_record.lea_id')
					->join('pnc_sub_leave', 'pnc_sub_leave.stl_id = pnc_record.stl_id')
					->join('pnc_roles', 'pnc_roles.role_id = pnc_employee.role_id')
					->where('pnc_record.staff_id', $staff_id)
					->where('pnc_leave_type.lea_alias', $leave_type)
					->where('pnc_record.lea_id', $lea_id)
					->count_all_results();
		} else if ($leave_type === 'catchup') {
			$query = $this->db
					->select('*')
					->from('pnc_record')
					->join('pnc_employee', 'pnc_record.staff_id = pnc_employee.staff_id')
					->join('pnc_department', 'pnc_employee.dept_id = pnc_department.dept_id')
					->join('pnc_leave_type', 'pnc_leave_type.lea_id = pnc_record.lea_id')
					->join('pnc_cup_des', 'pnc_record.catch_desc = pnc_cup_des.desc_id')
					->where('pnc_record.staff_id', $staff_id)
					->where('pnc_leave_type.lea_alias', $leave_type)
					->where('pnc_record.lea_id', $lea_id)
					->count_all_results();
		}
		return $query;
	}

	public function num_subtype($sub_leave, $staff_id) {
		$query = $this->db
				->select('*')
				->from('pnc_record')
				->join('pnc_employee', 'pnc_record.staff_id = pnc_employee.staff_id')
				->join('pnc_department', 'pnc_employee.dept_id = pnc_department.dept_id')
				->join('pnc_leave_type', 'pnc_leave_type.lea_id = pnc_record.lea_id')
				->join('pnc_sub_leave', 'pnc_sub_leave.stl_id = pnc_record.stl_id')
				->join('pnc_roles', 'pnc_roles.role_id = pnc_employee.role_id')
				->where('pnc_record.staff_id', $staff_id)
				->where('pnc_sub_leave.stl_name', $sub_leave)
				->where('rcd_status', 1)
				->get();
		return $query;
	}

	public function get_report($id, $user_type) {
		if ($user_type === 'admin') {
			$query = $this->db
					->select('*')
					->from('pnc_record')
					->join('pnc_employee', 'pnc_record.staff_id = pnc_employee.staff_id')
					->join('pnc_department', 'pnc_employee.dept_id = pnc_department.dept_id')
					->join('pnc_leave_type', 'pnc_leave_type.lea_id = pnc_record.lea_id')
					->join('pnc_sub_leave', 'pnc_sub_leave.stl_id = pnc_record.stl_id', 'left')
					->join('pnc_roles', 'pnc_roles.role_id = pnc_employee.role_id')
					->join('pnc_cup_des', 'pnc_record.catch_desc = pnc_cup_des.desc_id', 'left')
					->where('pnc_record.staff_id', $id)
					->where('pnc_record.rcd_status', 1)
					->where('pnc_roles.role_alias', 'admin')
					->get();
		} else if ($user_type === 'employee') {
			$query = $this->db
					->select('*')
					->from('pnc_record')
					->join('pnc_employee', 'pnc_record.staff_id = pnc_employee.staff_id')
					->join('pnc_department', 'pnc_employee.dept_id = pnc_department.dept_id')
					->join('pnc_leave_type', 'pnc_leave_type.lea_id = pnc_record.lea_id')
					->join('pnc_sub_leave', 'pnc_sub_leave.stl_id = pnc_record.stl_id', 'left')
					->join('pnc_roles', 'pnc_roles.role_id = pnc_employee.role_id')
					->join('pnc_cup_des', 'pnc_record.catch_desc = pnc_cup_des.desc_id', 'left')
					->where('pnc_record.staff_id', $id)
					->where('pnc_record.rcd_status', 1)
					->get();
		}

		if ($query->num_rows() > 0) {
			foreach ($query->result() as $report) {
				$data[] = $report;
			}
			return $data;
		}
		return FALSE;
	}

	public function sub_total($id, $lea_id, $leave_type) {
		$row = '';
		$return = '';
		if ($leave_type === 'annual' OR $leave_type === 'catchup') {
			$query = $this->db
					->select_sum('rcd.duration', 'total')
					->from('pnc_record rcd')
					->where("rcd.lea_id", $lea_id)
					->where('rcd.staff_id', $id)
					->where('rcd.rcd_status', 1)
					->get();
			$row = $query->row();
			return $return['sub_total_type'] = $row->total;
		} else if ($leave_type === 'impossed' OR $leave_type === 'personal' OR $leave_type === 'special') {
			$query = $this->db
					->select_sum('rcd.duration', 'total')
					->from('pnc_record rcd')
					->where("rcd.stl_id", $lea_id)
					->where('rcd.staff_id', $id)
					->where('rcd.rcd_status', 1)
					->get();
			$row = $query->row();
			return $return['sub_total'] = $row->total;
		}
	}
	 public function semester_1() {
        $query = $this->db
                ->select('*')
                ->from('pnc_employee')
                ->join('pnc_record','pnc_record.staff_id=pnc_employee.staff_id')
                ->join('pnc_department','pnc_department.dept_id=pnc_employee.dept_id')
//                ->join('pnc_group','pnc_group.grp_id=pnc_employee.grp_id')
                ->group_by('pnc_record.staff_id')
                ->group_by('pnc_employee.dept_id')
                ->where('YEAR( start_date ) = YEAR( CURDATE( ) ) and YEAR( end_date ) = YEAR( CURDATE( ) )')
                ->where('(month(start_date)>0 and month(start_date)<7 ) and (month(end_date)>0 and month(end_date)<7)')
               ->where('pnc_record.rcd_status',1)
                ->get();
        return$query;
        }
        public function semester_2() {
        $query = $this->db
                ->select('*')
                ->from('pnc_employee')
                ->join('pnc_record','pnc_record.staff_id=pnc_employee.staff_id')
                ->join('pnc_department','pnc_department.dept_id=pnc_employee.dept_id')
//                ->join('pnc_group','pnc_group.grp_id=pnc_employee.grp_id')
                ->group_by('pnc_record.staff_id')
                ->group_by('pnc_employee.dept_id')
                ->where('YEAR( start_date ) = YEAR( CURDATE( ) ) and YEAR( end_date ) = YEAR( CURDATE( ) )')
                ->where('( month(start_date)>6 and month(start_date)<13) and (month(end_date)>6) and month(end_date)<13')
               ->where('pnc_record.rcd_status',1)
                ->get();
        return$query;
        }
         public function monthly_report() {
        $query = $this->db
                ->select('*')
                ->from('pnc_employee')
                ->join('pnc_record','pnc_record.staff_id=pnc_employee.staff_id')
                ->join('pnc_department','pnc_department.dept_id=pnc_employee.dept_id')
//                ->join('pnc_group','pnc_group.grp_id=pnc_employee.grp_id')
                ->group_by('pnc_record.staff_id')
                ->group_by('pnc_employee.dept_id')
                ->where('pnc_record.rcd_status',1)
                ->where('month( start_date ) = month( CURDATE( ) ) and month( end_date ) = month( CURDATE( ) )')
                ->get();
        return$query;
        }
        public function yearly_report() {
        $query = $this->db
                ->select('*')
                ->from('pnc_employee')
                ->join('pnc_record','pnc_record.staff_id=pnc_employee.staff_id')
                ->join('pnc_department','pnc_department.dept_id=pnc_employee.dept_id')
//                ->join('pnc_group','pnc_group.grp_id=pnc_employee.grp_id')
                ->group_by('pnc_record.staff_id')
                ->group_by('pnc_employee.dept_id')
                ->where('pnc_record.rcd_status',1)
                ->where('YEAR( start_date ) = YEAR( CURDATE( ) ) and YEAR( end_date ) = YEAR( CURDATE( ) )')
                ->get();
        return$query;
        }
        
        function select_report(){
        $dat_from=$this->input->post('s_date');
         $dat_to=$this->input->post('e_date');
         $query = $this->db
                ->select('*')
                ->from('pnc_employee')
                ->join('pnc_record','pnc_record.staff_id=pnc_employee.staff_id')
                ->join('pnc_department','pnc_department.dept_id=pnc_employee.dept_id')
//                ->join('pnc_group','pnc_group.grp_id=pnc_employee.grp_id')
               ->group_by('pnc_record.staff_id')
                ->group_by('pnc_employee.dept_id')
                ->where('start_date >=', $dat_from)
                ->where('end_date <=', $dat_to)
                ->where('pnc_record.rcd_status',1)
                ->get();
        return$query;
    }
       
}

